Azure SQL Database must map the PKI-authenticated identity to an associated user account.

Overview

Finding ID	Version	Rule ID	IA Controls	Severity
V-255336	ASQL-00-008500	SV-255336r877266_rule		Medium

Description
The DOD standard for authentication is DOD-approved PKI certificates. Once a PKI certificate has been validated, it must be mapped to an Azure SQL Database user account for the authenticated identity to be meaningful to Azure SQL Database and useful for authorization decisions.

STIG	Date
Microsoft Azure SQL Database Security Technical Implementation Guide	2022-11-16

Details

Check Text ( C-59009r877264_chk )
To verify that Azure Active Directory is configured as the authentication type, use the following PowerShell commands: $LogicalServerName = "myServer" Get-AzSqlServer -ServerName $LogicalServerName \| Get-AzSqlServerActiveDirectoryOnlyAuthentication If AzureADOnlyAuthentication returns False, this is a finding.

Fix Text (F-58953r877265_fix)
To set the Azure Active Directory Administrator, use the following PowerShell command: $LogicalServerName = "myServer" Set-AzSqlServerActiveDirectoryAdministrator -ResourceGroupName "myResourceGroup" -ServerName $LogicalServerName -DisplayName "myAADIdentify" Azure Active Directory Authentication can be enabled using either PowerShell or the Azure CLI. To enable Azure Active Directory Authentication using PowerShell, use the commands below: ###### ###### Sets the AAD Admin in the SQL Server using PowerShell ###### ###### $LogicalServerName = "myServer" $ResourceGroup = "myResourceGroup" $DisplayName = "" $ObjectId = "" Set-AzSqlServerActiveDirectoryAdministrator ` -ResourceGroupName $ResourceGroup ` -ServerName $LogicalServerName ` -DisplayName $DisplayName ` -ObjectId$ObjectId #Sets AD Admin Only Get-AzSqlServer -ServerName $LogicalServerName ` \| Enable-AzSqlServerActiveDirectoryOnlyAuthentication To enable Azure Active Directory Authentication using the Azure CLI, use the commands below: ###### ###### Sets the AAD Admin in the SQL Server using the Azure CLI ###### ###### az sql server ad-admin create ` --resource-group $ResourceGroup --server $LogicalServerName ` --display-name $DisplayName ` --object-id $ObjectId ` #Sets AD Admin Only az sql server ad-only-auth enable ` --resource-group $ResourceGroup ` --name $LogicalServerName https://docs.microsoft.com/en-us/cli/azure/sql/server/ad-only-auth?view=azure-cli-latest https://docs.microsoft.com/en-us/azure/azure-sql/database/authentication-aad-configure?tabs=azure-powershell

Fix Text (F-58953r877265_fix)

To set the Azure Active Directory Administrator, use the following PowerShell command:

$LogicalServerName = "myServer"
Set-AzSqlServerActiveDirectoryAdministrator -ResourceGroupName "myResourceGroup" -ServerName $LogicalServerName -DisplayName "myAADIdentify"

Azure Active Directory Authentication can be enabled using either PowerShell or the Azure CLI.

To enable Azure Active Directory Authentication using PowerShell, use the commands below:

######
###### Sets the AAD Admin in the SQL Server using PowerShell ######
######
$LogicalServerName = "myServer"
$ResourceGroup = "myResourceGroup"
$DisplayName = ""
$ObjectId = ""

Set-AzSqlServerActiveDirectoryAdministrator `
-ResourceGroupName $ResourceGroup `
-ServerName $LogicalServerName `
-DisplayName $DisplayName `
-ObjectId$ObjectId

#Sets AD Admin Only
Get-AzSqlServer -ServerName $LogicalServerName `
| Enable-AzSqlServerActiveDirectoryOnlyAuthentication

To enable Azure Active Directory Authentication using the Azure CLI, use the commands below:

######
###### Sets the AAD Admin in the SQL Server using the Azure CLI ######
######
az sql server ad-admin create `
--resource-group $ResourceGroup
--server $LogicalServerName `
--display-name $DisplayName `
--object-id $ObjectId `

#Sets AD Admin Only
az sql server ad-only-auth enable `
--resource-group $ResourceGroup `
--name $LogicalServerName

https://docs.microsoft.com/en-us/cli/azure/sql/server/ad-only-auth?view=azure-cli-latest
https://docs.microsoft.com/en-us/azure/azure-sql/database/authentication-aad-configure?tabs=azure-powershell